from tortoise import BaseDBAsyncClient


async def upgrade(db: BaseDBAsyncClient) -> str:
    return """
        CREATE TABLE IF NOT EXISTS `product_categories` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '分类ID',
    `name` VARCHAR(100) NOT NULL COMMENT '分类名称',
    `icon` VARCHAR(200) COMMENT '分类图标',
    `sort_order` INT NOT NULL COMMENT '排序' DEFAULT 0,
    `is_active` BOOL NOT NULL COMMENT '是否启用' DEFAULT 1,
    `created_at` DATETIME(6) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `updated_at` DATETIME(6) NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
) CHARACTER SET utf8mb4 COMMENT='商品分类表';
CREATE TABLE IF NOT EXISTS `products` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '商品ID',
    `name` VARCHAR(200) NOT NULL COMMENT '商品名称',
    `description` LONGTEXT COMMENT '商品描述',
    `image` LONGTEXT COMMENT '商品图片URL',
    `points_price` INT NOT NULL COMMENT '积分价格',
    `stock` INT NOT NULL COMMENT '库存数量' DEFAULT 0,
    `is_special` BOOL NOT NULL COMMENT '是否特惠' DEFAULT 0,
    `sort_order` INT NOT NULL COMMENT '排序' DEFAULT 0,
    `status` VARCHAR(8) NOT NULL COMMENT '状态' DEFAULT 'active',
    `created_at` DATETIME(6) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `updated_at` DATETIME(6) NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `category_id` INT NOT NULL COMMENT '商品分类',
    CONSTRAINT `fk_products_product__a7f77cf2` FOREIGN KEY (`category_id`) REFERENCES `product_categories` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='商品表';
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
    `openid` VARCHAR(100) NOT NULL UNIQUE COMMENT '微信openid',
    `nickname` VARCHAR(100) COMMENT '昵称',
    `avatar` LONGTEXT COMMENT '头像URL',
    `phone` VARCHAR(20) COMMENT '手机号',
    `points` INT NOT NULL COMMENT '积分余额' DEFAULT 0,
    `checkin_days` INT NOT NULL COMMENT '累计打卡天数' DEFAULT 0,
    `consecutive_days` INT NOT NULL COMMENT '连续打卡天数' DEFAULT 0,
    `last_checkin` DATETIME(6) COMMENT '最后打卡时间',
    `created_at` DATETIME(6) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `updated_at` DATETIME(6) NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
) CHARACTER SET utf8mb4 COMMENT='用户表';
CREATE TABLE IF NOT EXISTS `detection_records` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '记录ID',
    `image_url` LONGTEXT NOT NULL COMMENT '阿里云OSS照片URL地址',
    `oss_path` VARCHAR(500) NOT NULL COMMENT 'OSS存储路径',
    `result` JSON COMMENT 'AI检测结果(BASP分型、严重程度、建议等JSON格式)',
    `is_checkin` BOOL NOT NULL COMMENT '是否已打卡' DEFAULT 0,
    `created_at` DATETIME(6) NOT NULL COMMENT '检测时间' DEFAULT CURRENT_TIMESTAMP(6),
    `user_id` INT NOT NULL COMMENT '关联用户',
    CONSTRAINT `fk_detectio_users_31ece51c` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='检测记录表';
CREATE TABLE IF NOT EXISTS `orders` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '订单ID',
    `order_no` VARCHAR(50) NOT NULL UNIQUE COMMENT '订单号',
    `product_name` VARCHAR(200) NOT NULL COMMENT '商品名称',
    `product_image` LONGTEXT COMMENT '商品图片',
    `points_used` INT NOT NULL COMMENT '使用积分',
    `quantity` INT NOT NULL COMMENT '数量' DEFAULT 1,
    `status` VARCHAR(9) NOT NULL COMMENT '订单状态' DEFAULT 'pending',
    `shipping_info` JSON NOT NULL COMMENT '收货信息JSON',
    `remark` LONGTEXT COMMENT '备注',
    `shipped_at` DATETIME(6) COMMENT '发货时间',
    `completed_at` DATETIME(6) COMMENT '完成时间',
    `created_at` DATETIME(6) NOT NULL COMMENT '订单时间' DEFAULT CURRENT_TIMESTAMP(6),
    `updated_at` DATETIME(6) NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `product_id` INT NOT NULL COMMENT '关联商品',
    `user_id` INT NOT NULL COMMENT '关联用户',
    CONSTRAINT `fk_orders_products_462a7b0f` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_orders_users_411bb784` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='订单表';
CREATE TABLE IF NOT EXISTS `aerich` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `version` VARCHAR(255) NOT NULL,
    `app` VARCHAR(100) NOT NULL,
    `content` JSON NOT NULL
) CHARACTER SET utf8mb4;"""


async def downgrade(db: BaseDBAsyncClient) -> str:
    return """
        """
